package com.yeren.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.yeren.dao.WeixinUserInfoDao;
import com.yeren.entity.WeixinUserInfo;
import com.yeren.utils.DBUtil;
import com.yeren.utils.PageBean;
import com.yeren.utils.StringUtil;

/**
 * 微信用户操作实现
 * 
 * @author liubiao
 *
 */
public class WeixinUserInfoDaoImpl implements WeixinUserInfoDao{
	Connection conn=null;
	PreparedStatement pstmt=null;
	ResultSet rs=null;
	private static Logger logger = Logger.getLogger(UserDaoImpl.class);
	
	
	
	@Override
	public int save(WeixinUserInfo weixinUserInfo) {
		String sql="insert into weixin_user_info values(?,?,?,?,?,?,?,?,?,?,?)";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, weixinUserInfo.getOpenId());
				pstmt.setInt(2, weixinUserInfo.getSubscribe());
				pstmt.setString(3, weixinUserInfo.getSubscribeTime());
				pstmt.setString(4, weixinUserInfo.getNickname());
				pstmt.setInt(5, weixinUserInfo.getSex());
				pstmt.setString(6, weixinUserInfo.getCountry());
				pstmt.setString(7, weixinUserInfo.getProvince());
				pstmt.setString(8, weixinUserInfo.getCity());
				pstmt.setString(9, weixinUserInfo.getLanguage());
				pstmt.setString(10, weixinUserInfo.getHeadImgUrl());
				pstmt.setString(11, weixinUserInfo.getPrivileges());
				
				int save = pstmt.executeUpdate();
				if(save==1){
					logger.info("保存成功!");
				}
				return save;
				
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(null, pstmt, conn);
			}
		}
		return 0;
	}

	@Override
	public int delete(String openId) {
		String sql="delete from weixin_user_info where openid=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, openId);
				int delete = pstmt.executeUpdate();
				if(delete==1){
					logger.info("删除成功");
				}
				return delete;
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(null, pstmt, conn);
			}
			
		}
		return 0;
	}

	@Override
	public WeixinUserInfo findByOpenId(String openId) {
		String sql="select * from weixin_user_info where openid=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, openId);
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					WeixinUserInfo weixinUserInfo=new WeixinUserInfo();
					weixinUserInfo.setOpenId(rs.getString(1));
					weixinUserInfo.setSubscribe(rs.getInt(2));
					weixinUserInfo.setSubscribeTime(rs.getString(3));
					weixinUserInfo.setNickname(rs.getString(4));
					weixinUserInfo.setSex(rs.getInt(5));
					weixinUserInfo.setCountry(rs.getString(6));
					weixinUserInfo.setProvince(rs.getString(7));
					weixinUserInfo.setCity(rs.getString(8));
					weixinUserInfo.setLanguage(rs.getString(9));
					weixinUserInfo.setHeadImgUrl(rs.getString(10));
					weixinUserInfo.setPrivileges(rs.getString(11));
					return weixinUserInfo;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return null;
	}

	@Override
	public List<WeixinUserInfo> findAll(WeixinUserInfo weixinUserInfo,
			PageBean pageBean) {
		StringBuffer sb=new StringBuffer("select * from weixin_user_info w where 1=1");
		if(StringUtil.isNotEmpty(weixinUserInfo.getNickname())){
			sb.append(" and w.nickname like '%"+weixinUserInfo.getNickname()+"%'");
		}
		if(weixinUserInfo.getProvince()!=null){
			sb.append(" and w.province like '%"+weixinUserInfo.getProvince()+"%'");
		}
		if(pageBean!=null){
			sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
		}
		conn=DBUtil.getConn();
		List<WeixinUserInfo> list=new ArrayList<WeixinUserInfo>();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sb.toString());
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					WeixinUserInfo wui=new WeixinUserInfo();
					wui.setOpenId(rs.getString(1));
					wui.setSubscribe(rs.getInt(2));
					wui.setSubscribeTime(rs.getString(3));
					wui.setNickname(rs.getString(4));
					wui.setSex(rs.getInt(5));
					wui.setCountry(rs.getString(6));
					wui.setProvince(rs.getString(7));
					wui.setCity(rs.getString(8));
					wui.setLanguage(rs.getString(9));
					wui.setHeadImgUrl(rs.getString(10));
					wui.setPrivileges(rs.getString(11));
					list.add(wui);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return list;
	}

	@Override
	public boolean isExitOpenId(String openId) {
		String sql="select * from weixin_user_info where openid=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,openId);
				ResultSet rs = pstmt.executeQuery();
				while(rs.next()){
					logger.info("用户已存在");
					return true;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return false;
	}

	@Override
	public int update(WeixinUserInfo weixinUserInfo) {
		String sql="update weixin_user_info set openid=?,subscribe=?,subscribeTime=?,nickname=?,sex=?,country=?,province=?,city=?,language=?,headImgUrl=?,privileges=? where openid=?";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1, weixinUserInfo.getOpenId());
				pstmt.setInt(2,weixinUserInfo.getSubscribe());
				pstmt.setString(3, weixinUserInfo.getSubscribeTime());
				pstmt.setString(4, weixinUserInfo.getNickname());
				pstmt.setInt(5,weixinUserInfo.getSex());
				pstmt.setString(6, weixinUserInfo.getCountry());
				pstmt.setString(7, weixinUserInfo.getProvince());
				pstmt.setString(8, weixinUserInfo.getCity());
				pstmt.setString(9, weixinUserInfo.getLanguage());
				pstmt.setString(10, weixinUserInfo.getHeadImgUrl());
				pstmt.setString(11, weixinUserInfo.getPrivileges());
				
				int update = pstmt.executeUpdate();
				if(update==1){
					logger.info("修改成功");
				}
				return update;
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{}
			DBUtil.close(null, pstmt, conn);
		}
		return 0;
	}

	public int getSum() {
		String sql="select count(*) from weixin_user_info";
		conn=DBUtil.getConn();
		if(conn!=null){
			try {
				pstmt=conn.prepareStatement(sql);
				ResultSet rs = pstmt.executeQuery();
				
				while(rs.next()){
					int num = rs.getInt(1);
					return num;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally{
				DBUtil.close(rs, pstmt, conn);
			}
		}
		return 0;
	}

}
